﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Bilyteam_Website;

namespace ClassLibrary1.DAO
{
    public class MenuDAO
    {
        public MenuDAO()
        {
        }
        #region SQLQUERIES

        public const string SQL_SELECT_MENUS = "SELECT * FROM Menus WHERE [Status] = 0 ORDER BY MenuID ";
        public const string SQL_SELECT_BY_ID_MENUS = "SELECT * FROM Menus WHERE MenuID = @MenuID";
        public const string SQL_INSERT_MENUS = "INSERT INTO Menus(MenuName, Status) VALUES(@MenuName, @Status)";
        public const string SQL_UPDATE_MENUS = "UPDATE Menus SET MenuName = @MenuName WHERE MenuID=@MenuID";
        public const string SQL_DELETE_MENUS = "UPDATE Menus Set Status=@Status WHERE MenuID=@MenuID";


        #endregion
        #region Parameter
        public const string PARMS_MenuID = "@MenuID";
        public const string PARMS_MenuName = "@MenuName";
        public const string PARMS_Status = "Status";
        #endregion
        private Menus MenuManipulate(SqlDataReader reader)
        {
            Menus men = new Menus();
            if (!reader.IsDBNull(0))
                men.MenuID = reader.GetInt32(0);
            if (!reader.IsDBNull(1)) ;
            men.MenuName = reader.GetString(1);
            if (!reader.IsDBNull(2))
                men.Status = reader.GetBoolean(2);
            return men;
        }
        public IList<Menus> GetAll()
        {
            SqlParameter[] parms;
            parms = new SqlParameter[] { new SqlParameter(PARMS_Status, SqlDbType.Bit), };
            parms[0].Value = 0;
            IList<Menus> menulist = new List<Menus>();
            using (SqlDataReader reader = SQLDBHelper.ExecuteReader(SQLDBHelper.CONN_STRING, CommandType.Text, SQL_SELECT_MENUS, parms))
            {
                while (reader.Read())
                {
                    Menus mem = MenuManipulate(reader);
                    menulist.Add(mem);
                }

            }
            return menulist;
        }
        public bool Insert(Menus mem)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]
                       {
                           new SqlParameter(PARMS_MenuName,SqlDbType.NVarChar),
                           new SqlParameter(PARMS_Status,SqlDbType.Bit)};
            parms[0].Value = mem.MenuName;
            parms[1].Value = mem.Status;
            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn,
                    CommandType.Text, SQL_INSERT_MENUS, parms);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {

                throw e;
            }
        }
        public bool Update(Menus mem)
        {
            SqlParameter[] parms;
            parms = new SqlParameter[]
                       {
                           new SqlParameter(PARMS_MenuID,SqlDbType.Int),
                           new SqlParameter(PARMS_MenuName,SqlDbType.NVarChar),
                           new SqlParameter(PARMS_Status,SqlDbType.Bit)
                       };
            parms[0].Value = mem.MenuID;
            parms[1].Value = mem.MenuName;
            parms[2].Value = mem.Status;
            try
            {
                int result = 0;
                SqlConnection conn = new SqlConnection(SQLDBHelper.CONN_STRING);
                result = SQLDBHelper.ExecuteNonQuery(conn, CommandType.Text, SQL_UPDATE_MENUS, parms);
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                return (result > 0 ? true : false);
            }
            catch (Exception)
            {

                throw;
            }


        }
        public bool Delete(int id)
        {
            SqlParameter []parms;
            parms= new SqlParameter[]
                       {
                           new SqlParameter(PARMS_MenuID,SqlDbType.Int),
                           new SqlParameter(PARMS_Status,SqlDbType.Bit)};
            parms[0].Value = id;
            parms[1].Value = 1;
            try
            {
                int result = 0;
                result = SQLDBHelper.ExecuteNonQuery(SQLDBHelper.CONN_STRING,
                                                     CommandType.Text, SQL_DELETE_MENUS, parms);
                return (result > 0 ? true : false);
            }
            catch (Exception e)
            {
                
                throw e;
            }
            return false;
        }


    }
}
